import sqlite3
class Database:
    def __init__(self, databaseName="puffin"):
        self.sql = sqlite3.connect('puffin') #connects to database. creates it if it does not exist.
        self.queryCurs = self.sql.cursor() #cursor allows you to run queries.

    def select(self, table_name, params, requested_fields="*"):
        #table_name = str
        #parameters = list containing strings or a string
        #requested_fields = list containing strings or a string
        parameters = "" #initialize paramters variable
        fields = "" #initilize fields variable
        if type(params) == list:
            for i in params:
                parameters += (" AND "+i)
            parameters = parameters.lstrip(' AND ') #lstrip removes the first " AND " from the columns string so that SQL statement is properly formatted.
        elif type(params) == str: parameters = params
        if type(requested_fields) == list:
            for i in requested_fields:
                fields += (", "+i)
            fields = fields.lstrip(', ') #lstrip removes the first " AND " from the columns string so that SQL statement is properly formatted.
        elif type(requested_fields) == str: fields = requested_fields
        data = self.queryCurs.execute("SELECT "+fields+" FROM "+table_name+" WHERE "+params)
        return data

    def insert(self, table_name, key_value):
        #table_name = str. This is the name of the database table
        #key_value = dict. This is the key: value paris, where the key is database table column, and the value is the value to be updated for that column.
        #key_value = Model.dateHelp(key_value) #if I want convert a string to a dictionary
        columns = "" #initialize the columns variable. This will hold the names of the database table columns
        valuesList = [] #initialize the list for values. This will hold the values that correspond to the database table columns
        var = "" #Python SQL sanitation is ... peculiar. In short I need an equal number of question marks ("?") as I have values to insert. This variable will hold the "?" vars for the SQL statement.
        for i in key_value: #Now, let's turn our key:value pairs into an SQL statement. The for loop will transform keys and values into separate ordered lists, so that the keys and values correspond positionally.
            if (key_value[i] != None) & (key_value[i] !="") & (key_value[i]!=" "): #Make sure that every key has a meaningful value
                columns += (", "+i) #Add the key/column_name to the columns variable
                #Now, we add the corresponding value to the valuesList.
                #Remember that Python SQL sanitation is peculiar. The values must be contained in a list, instead of as a string.
                valuesList.append(key_value[i].strip())
                var += ", ?" #Add a question mark for every value to be inserted in order for SQL sanitation to work.
        columns = columns.lstrip(', ') #lstrip removes the first ", " from the columns string so that SQL statement is properly formatted.
        var = var.strip(', ')
        self.queryCurs.execute("INSERT INTO "+table_name+" ("+columns+") VALUES ("+var+")", valuesList) #This executes a properly formatted, sanitized SQL statement
        self.sql.commit() #Commit the changes to the hard disk.

    def update(self, table_name, key_value, primary_key="id"):
        #table_name = str. This is the name of the database table
        #key_value = dict. This is the key: value paris, where the key is database table column, and the value is the value to be updated for that column.
        #primary_key = str or None. This is the primary key of the database table
        #key_value = Model.dateHelp(key_value) #This where you should call any helpder functions
        valuesList = [] #initialize the list for values. This will hold the values that correspond to the database table columns
        #SQL UPDATE is slightly different from SQL INSERT. Thus, instead of initializing a columns variable and a var variable (see insert()); we combine both into the command variable
        command=""
        for i in key_value: #Now, let's turn our key:value pairs into an SQL statement. The for loop will transform keys and values into separate ordered lists, so that the keys and values correspond positionally.
            if (key_value[i] != None) & (key_value[i] !="") & (key_value[i]!=" ") & (i!=primary_key): #make sure that the key has a meaningful value, and that the key i not the primary key
                command += (", "+i+" = ?") #Instead of adding the corresponding value, we are adding a question mark (?) so that sqlite module can sanitize the data.
                valuesList.append(key_value[i].strip())
        command = command.lstrip(', ') #Removes the leading comma in the command string.
        command = 'UPDATE calendar SET '+command+" WHERE "+primary_key+"='"+key_value[primary_key]+"'"
        self.queryCurs.execute(command, valuesList) #Execute a sanitized SQL statement.
        self.sql.commit() #Commit the changes to the hard disk.

    def delete (self, table_name, key="id", value="None"):
        #table_name = str
        #key = str
        #value = str
        self.queryCurs.execute("DELETE FROM "+table_name+" WHERE "+key+"='"+value+"'")
        self.sql.commit()



##    tableColumns = ('id', 'date', 'begin_time', 'end_time', 'heading', 'details',
##           'location', 'invitees', 'attendees',
##           'case_id', 'assignment_type', 'assignment_id', 'work_product_url')




    def create_table(self, database_name):  
        #Try and create the table.  
        #If it fails, it's probably because the table exists  
        #try:  
            table_sql = '''CREATE TABLE calendar
            (event_id INTEGER PRIMARY KEY,
            date VARCHAR(255) NOT NULL,
            year INT(5),
            month INT(2),
            day INT(2),
            heading VARCHAR(255) NOT NULL,
            details TEXT,
            begin_time VARCHAR(255), 
            end_time VARCHAR(255),
            location TEXT,
            invitees TEXT,
            attendees TEXT,
            case_id INT,
            assignment_type VARCHAR(255),
            assignment_id INT,
            work_product_url TEXT,
            sequence BOOL,
            sequence_ancestors TEXT,
            sequence_dependents TEXT,
            removable BOOL,
            restricted_modification INT
            )'''
      
            #Open a connection to the SQLite database and create the table  
            sql_connection = Database.sqlite3.connect(database_name)  
            sql_cursor = sql_connection.cursor()  
            sql_cursor.execute(table_sql)
            sql_connection.commit()  
            sql_connection.close()  
        #except:  
        #    print('did not work')
#a = Database()
#a.create_table("puffin")
#a.addEvent({"heading":"b","date":"d"})
